package dao;

import entity.Sale;
import entity.SaleDetails;
import entity.Supplier;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SaleDao extends BaseDao {
    //列表所有销售单信息
    public List getAllsale() throws Exception {
        List saleList = new ArrayList();
        String sql = "select saleId,userId,handlerName,saleTotalPrice,saleDate" +
                " from sale";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String saleId = resultSet.getString("saleId");
            String userId = resultSet.getString("userId");
            String handlerName = resultSet.getString("handlerName");
            float salePrice = resultSet.getFloat("saleTotalPrice");
            String  saleDate = resultSet.getString("saleDate");
            Sale sale = new Sale(saleId,userId,handlerName,salePrice,saleDate);
            saleList.add(sale);
        }
        return saleList;
    }

    //录入订单信息
    public boolean addSale(Sale sale) throws Exception {
        String sql = "insert into sale(saleId,userId,handlerName,saleTotalPrice,saleDate)" +
                " VALUES(?,?,?,?,?)";
        Connection connection = getConnection();//获取数据库连接
        PreparedStatement preparedStatement = connection.prepareStatement(sql);//创建PreparedStatement
        preparedStatement.setString(1, sale.getSaleId());
        //设置第一个？占位符的值
        preparedStatement.setString(2, sale.getUserId());
        //设置第二个？占位符的值
        preparedStatement.setString(3, sale.getHandlerName());
        //设置第三个？占位符的值
        preparedStatement.setFloat(4, sale.getSaleTotalPrice());
        //设置第四个？占位符的值
        preparedStatement.setString(5, sale.getSaleDate());
        //设置第五个？占位符的值

        int num = preparedStatement.executeUpdate();//执行插入sql语句
        if (num==1)
        {
            return true;//执行成功
        }else {
            return false;//执行失败
        }
    }

    //删除订单信息
    public String deleteSale(Sale sale) throws Exception {
        String result= null;
        Connection connection = getConnection();//获取数据库连接

        String sql = "delete from sale where saleId=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,sale.getSaleId());
        int num = preparedStatement.executeUpdate();//执行删除sql操作
        if(num==1){
            //删除成功
            result = "删除成功";
        }else {
            //删除失败
            result = "删除失败";
        }
        return result;
    }

    //列表指定用户销售单信息
    public List getAppointSale(String userIdTemp) throws Exception {
        List saleList = new ArrayList();
        String sql = "select saleid,userid,handlerName,saleTotalPrice,saleDate" +
                " from sale where userid=" + userIdTemp;
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String saleId = resultSet.getString("saleId");
            String userId = resultSet.getString("userId");
            String handlerName = resultSet.getString("handlerName");
            float saleTotalPrice = resultSet.getFloat("saleTotalPrice");
            String  saleDate = resultSet.getString("saleDate");
            Sale sale = new Sale(saleId,userId,handlerName,saleTotalPrice,saleDate);
            saleList.add(sale);
        }
        return saleList;
    }

    //列表指定编号销售单信息
    public Sale getAppointSale2(String saleIdTemp) throws Exception {
        Sale sale = new Sale();
        String sql = "select saleId,userId,handlerName,saleTotalPrice,saleDate" +
                " from sale where saleId='" + saleIdTemp + "'";
        Connection connection = getConnection();//获取数据库连接
        Statement statement = connection.createStatement();//创建statement对象
        ResultSet resultSet = statement.executeQuery(sql);//执行查询sql
        while(resultSet.next()){
            String saleId = resultSet.getString("saleId");
            String userId = resultSet.getString("userId");
            String handlerName = resultSet.getString("handlerName");
            float saleTotalPrice = resultSet.getFloat("saleTotalPrice");
            String  saleDate = resultSet.getString("saleDate");
            sale = new Sale(saleId,userId,handlerName,saleTotalPrice,saleDate);
        }
        return sale;
    }

    public static void main(String[] args) throws Exception {
        SaleDao saleDao = new SaleDao();
        System.out.println(saleDao.getAppointSale("sx202301"));
    }
}
